This article is for those who have no idea about Sql and I will be giving them a start point from there they can build their knowledge base. Since in this article time is key, so I will be using images as many as possible and fewer words because as you may know time is key here.
Let's start with creating tables and inserting some data because we need table to query them.These two tables we will be using throughout this article. Copy and paste below code in your Sql Server Management Studio and Run it by Pressing F5 or UI Icon. Below script shows how to create a table and insert records.
-- Table Name is Employee
-- Column data type shows what kind of data you will be stroing in them. Data types are beyond this topic. I will be writing a separate post for it.
Create Table Employee
(
ID int, -- Column name is ID and data type is Int
Name varchar(256), -- Column name is Name and data type is varchar
)
Create Table EmpContact
(
ID int,
email varchar(256),
MobileNo varchar(10)
)
Insert into Employee values (1,'Dev')
Insert into Employee values (2,'Ram')
Insert into Employee values (3,'Sid')
Insert into Employee values (4,'Raj')
insert into EmpContact values(1,'dev@gmail.com',1111)
insert into EmpContact values(2,'ram@gmail.com',2222)
insert into EmpContact values(4,'raj@gmail.com',4444)
insert into EmpContact values(5,'gem@gmail.com',5555)
Learning – You learned, how to create table and insert data into tables.
Now we have tables with data and we are ready to write queries, let’s start with the simple one.
Select * from Employee
Output -
ID
|
Name
|
1
|
Dev
|
2
|
Ram
|
3
|
Sid
|
4
|
Raj
|
Here * is a wildcard character which means all the columns. So this query says return all the column data from table Employee. You can mention column names instead of *.
Select ID,Name from Employee
ID
|
Name
|
1
|
Dev
|
2
|
Ram
|
3
|
Sid
|
4
|
Raj
|
If you want only list of Names, then
Select Name from Employee
Name
|
Dev
|
Ram
|
Sid
|
Raj
|
Filtering Records -
Suppose you want specific records like return result for Employee whose ID is 1.
select * from Employee Emp Where Emp.ID=1
ID
|
Name
|
1
|
Dev
|
Select Employee whose name is ‘Raj’.
select * from Employee Emp Where Emp.Name='Raj'
ID
|
Name
|
4
|
Raj
|
Combining two conditions -
select * from Employee Emp Where Emp.Name='Raj' AND Emp.ID=4
ID
|
Name
|
4
|
Raj
|
Learning – now you know how to select records from a table and how to apply filter criteria.
How Boolean operators works –
True AND True = True, True AND False=False, False AND True=False, False AND False=False
True OR True = True, True OR False= True, False OR True= True, False OR False=False
Next Topic – Joins
Suppose you want to fetch the information from database but it is spread in multiple tables. For keeping it simple we will use only two tables Employee and EmpContact.
Joins, as the name suggest it joins two tables on joining columns. Suppose we want to join our two tables on the ID column. If sql server picks the ID value from first table and search it in other table. If it finds the match it returns the corresponding record. Take a look at below image -
Select * from Employee Emp inner join EmpContact EmpC on Emp.ID=EmpC.ID
ID
|
Name
|
ID
|
email
|
MobileNo
|
1
|
Dev
|
1
|
dev@gmail.com
|
1111
|
2
|
Ram
|
2
|
ram@gmail.com
|
2222
|
4
|
Raj
|
4
|
raj@gmail.com
|
4444
|
Left Join – It returns all the records from left table and matching records from right one. If it could not find match in right table , it returns NULL.
Select * from Employee Emp left join EmpContact EmpC on Emp.ID=EmpC.ID
ID
|
Name
|
ID
|
email
|
MobileNo
|
1
|
Dev
|
1
|
dev@gmail.com
|
1111
|
2
|
Ram
|
2
|
ram@gmail.com
|
2222
|
3
|
Sid
|
NULL
|
NULL
|
NULL
|
4
|
Raj
|
4
|
raj@gmail.com
|
4444
|
Right Join – It behaves exactly like left only difference is right side tables return all records and left one matching records or NULL if no match found.
Select * from Employee Emp right join EmpContact EmpC on Emp.ID=EmpC.ID
ID
|
Name
|
ID
|
email
|
MobileNo
|
1
|
Dev
|
1
|
dev@gmail.com
|
1111
|
2
|
Ram
|
2
|
ram@gmail.com
|
2222
|
4
|
Raj
|
4
|
raj@gmail.com
|
4444
|
NULL
|
NULL
|
5
|
gem@gmail.com
|
5555
|
Full Outer Join – It returns all the records from both tables , if it doesn’t find the match it returns null.
Select * from Employee Emp full outer join EmpContact EmpC on Emp.ID=EmpC.ID
ID
|
Name
|
ID
|
email
|
MobileNo
|
1
|
Dev
|
1
|
dev@gmail.com
|
1111
|
2
|
Ram
|
2
|
ram@gmail.com
|
2222
|
3
|
Sid
|
NULL
|
NULL
|
NULL
|
4
|
Raj
|
4
|
raj@gmail.com
|
4444
|
NULL
|
NULL
|
5
|
gem@gmail.com
|
5555
|
Cross Join – It returns the Cartesian Product of both tables means,
All records in table one multiply by send table all records.
Select * from Employee Emp cross join EmpContact EmpC
ID
|
Name
|
ID
|
email
|
MobileNo
|
1
|
Dev
|
1
|
dev@gmail.com
|
1111
|
2
|
Ram
|
1
|
dev@gmail.com
|
1111
|
3
|
Sid
|
1
|
dev@gmail.com
|
1111
|
4
|
Raj
|
1
|
dev@gmail.com
|
1111
|
1
|
Dev
|
2
|
ram@gmail.com
|
2222
|
2
|
Ram
|
2
|
ram@gmail.com
|
2222
|
3
|
Sid
|
2
|
ram@gmail.com
|
2222
|
4
|
Raj
|
2
|
ram@gmail.com
|
2222
|
1
|
Dev
|
4
|
raj@gmail.com
|
4444
|
2
|
Ram
|
4
|
raj@gmail.com
|
4444
|
3
|
Sid
|
4
|
raj@gmail.com
|
4444
|
4
|
Raj
|
4
|
raj@gmail.com
|
4444
|
1
|
Dev
|
5
|
gem@gmail.com
|
5555
|
2
|
Ram
|
5
|
gem@gmail.com
|
5555
|
3
|
Sid
|
5
|
gem@gmail.com
|
5555
|
4
|
Raj
|
5
|
gem@gmail.com
|
5555
|
Next Topic – Grouping and Sorting
When we select records from a table we get in random order, if we need records to follow some specific order we need to specify it in the query. Default order is ascending. Suppose we need employee details in descending order of their ID. So this is how we ask database to sort them
select * from Employee Emp Order by ID desc
ID
|
Name
|
4
|
Raj
|
3
|
Sid
|
2
|
Ram
|
1
|
Dev
|
Now lets sort in ascending order -
select * from Employee Emp Order by ID Asc
ID
|
Name
|
1
|
Dev
|
2
|
Ram
|
3
|
Sid
|
4
|
Raj
|
Before we start grouping lets delete our old employee table and create a new one.
Drop Table Employee /* This is how you can delete a table if it’s not related to another table. Relationship between tables altogether is a different topic. */
Create Table Employee
(
ID int,
Name varchar(256),
Location varchar(256)
)
Insert into Employee values (1,'Dev','USA')
Insert into Employee values (2,'Ram','China')
Insert into Employee values (3,'Sid','USA')
Insert into Employee values (4,'Raj','USA')
Insert into Employee values (5,'Rick','China')
Insert into Employee values (6,'Rajesh','India')
Learning – you might have notice we use ‘--’ for inline comments and ‘/*’ and ‘*/’ for multi line comments.
Select ID,Name,Location from Employee Group by Location,Name,ID
When we use all columns in group by it works as order by.
ID
|
Name
|
Location
|
2
|
Ram
|
China
|
5
|
Rick
|
China
|
6
|
Rajesh
|
India
|
1
|
Dev
|
USA
|
4
|
Raj
|
USA
|
3
|
Sid
|
USA
|
Select Location,Count(Name) As ‘Count_Emp’ from Employee Group By Location
It required to use aggregate function when you use group by.
Location
|
Count_Emp
|
China
|
2
|
India
|
1
|
USA
|
3
|
We can filter records while using group by commands, using Having clause.
Select Location,Count(Name) As ‘Count_Emp’ from Employee Group By Location Having Location='China'
Location
|
Count_Emp
|
China
|
2
|
I hope now you have an idea about how to write simple Sql code. I agreed there is a lot of topics not discuss enough but I did so just to keep it short and fast pace crash course.
Recommended Topic - Books VS Internet
