If you are new to SQL, don't worry. You have come to the right place.
In this Step by Step tutorial, we will get started with writing simple SQL Queries to select data and then move on to sorting, filtering this data.
We will then learn how to perform aggregations and transformations on this data. And this is not it. We will also perform some joins and write our first subqueries!
And the best part is - you do not need any prior knowledge of SQL for this. The only thing you need is SQL Server installed on your machine. It has a free version that can be easily downloaded and installed.
You can download it from the official Microsoft site.
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
For Step by Step guidance, check out this tutorial on SQL Server Installation.
https://www.youtube.com/watch?v=ncj0EDzy_rw
Before we get started, we will need some sample data in our database. We are going to create some tables.
We will first create a database and then we will create our tables. You can copy-paste the below SQL Code to create our sample tables and data.
CREATE DATABASE Training;
USE [Training]
GO
CREATE TABLE [dbo].[Dept](
[DeptID] [int] NULL,
[DeptName] [varchar](50) NULL
) ;
CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] NOT NULL,
[FullName] [nvarchar](250) NOT NULL,
[DeptID] [int] NULL,
[Salary] [int] NULL,
[HireDate] [date] NULL,
[ManagerID] [int] NULL
) ;
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (1, N'HR')
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (2, N'Sales')
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (3, N'Marketing')
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (4, N'Finance')
GO
INSERT [dbo].[Dept] ([DeptID], [DeptName]) VALUES (5, NULL)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (1, N'Owens, Kristy', 1, 35000, CAST(N'2018-01-22' AS Date), 3)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (2, N'Adams, Jennifer', 1, 55000, CAST(N'2017-10-25' AS Date), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (3, N'Smith, Brad', 1, 110000, CAST(N'2015-02-02' AS Date), 7)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (4, N'Ford, Julia', 2, 75000, CAST(N'2019-08-30' AS Date), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (5, N'Lee, Tom', 2, 110000, CAST(N'2018-10-11' AS Date), 7)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (6, N'Jones, David', 3, 85000, CAST(N'2012-03-15' AS Date), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (7, N'Miller, Bruce', 1, 100000, CAST(N'2014-11-08' AS Date), NULL)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (9, N'Peters, Joe', 3, 11000, CAST(N'2020-03-09' AS Date), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (10, N'Joe, Alan', 3, 11500, CAST(N'2020-03-09' AS Date), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FullName], [DeptID], [Salary], [HireDate], [ManagerID]) VALUES (11, N'Clark, Kelly', 2, 11500, CAST(N'2020-03-09' AS Date), 5)
GO
Now that we have the sample data to work with, lets get started with writing SQL Queries -
1) Select all columns from a table. Use the Select statement followed by *. * here indicates we are selecting all columns. Then simply use the keyword FROM followed by the Table Name (Employees - in our case).
Select * FROM Employees
2) Select specific columns - Write the column names instead of *. In case of multiple columns, separate them by a comma (,).
Select EmployeeID, FullName FROM Employees
3) Sort the data by the values in a column - You can use ORDER BY clause followed by the column name based on which you wish to sort the data. By default, sorting is performed in an ascending manner.
Select EmployeeID, FullName FROM Employees
ORDER BY FullName
To perform sorting in a descending manner, mention DESC specifically in the ORDER BY clause.
Select EmployeeID, FullName FROM Employees
ORDER BY FullName DESC
4) Select topmost n rows - You can use TOP n to select the topmost n rows.
The below SQL Query selects topmost 2 rows from Employees table sorted by FullName.
Select Top 2 * FROM Employees
ORDER BY FullName
5) Filter Data - Use the WHERE clause to filter data. Mention the filtering condition after the WHERE clause.
The below query filters and outputs only those rows from Employees table for which DeptID column has value 1.
Select * FROM Employees
WHERE DeptID = 1
You can combine multiple conditions in WHERE clause. You can use operators AND / OR for combining multiple conditions
Select * FROM Employees
WHERE DeptID = 1 AND Salary >= 50000
Select * FROM Employees
WHERE DeptID = 1 OR Salary >= 50000
Select * FROM Employees
WHERE DeptID = 1 OR Salary BETWEEN 50000 AND 100000
Select * FROM Employees
WHERE DeptID = 1 OR (Salary >= 50000 AND Salary <= 100000)
6) Checking for NULL values - If you want to select rows where a particular column has NULL as its value, you need to use the IS NULL statement for comparison. IS NOT NULL will check for the column having Not NULL values.
Select * FROM Employees
WHERE ManagerID IS NULL
Select * FROM Employees
WHERE ManagerID IS NOT NULL
7) Matching data - Use the LIKE keyword to find data matching a pattern. For ex - the below SQL Query, finds all records where FullName has the alphabet 'A' in it. You need to use wildcards for pattern matching. % here means any number of characters having any value.
Select * FROM Employees
WHERE FullName LIKE '%A%'
8) List of Values - If you need to check on multiple values for a column, instead of writing multiple OR conditions as below -
Select * FROM Employees
WHERE DeptID = 1 OR DeptID = 2
You can use the IN clause followed by the list of values within round brackets.
Select * FROM Employees
WHERE DeptID IN (1,2)
9) Unique Records - To select unique records from a table, use the DISTINCT keyword. The column names specified after DISTINCT need to have the unique combination of values.
For ex - in the below SQL statement, distinct values for the combination of DeptID and ManagerID from Employees table will be returned on the output.
Select DISTINCT DeptID , ManagerID FROM Employees
10) Aggregate Functions - You can count the number of records in a table by using Count(*)
Select Count(*) as Cnt FROM Employees
11) Group BY - Instead of counting the total records in a table, you might need to count the total records for a particular category.
For ex - in the below statement, we need to count the number of Employees for each Dept.
In this situation. we need to use a GROUP BY statement followed by the column name(category) for which we need to perform the count. We can perform aother aggregate functions like Max EmployeeID for Each DeptID, Min EmployeeID etc as shown below.
Finally, after computing these aggregate values for each DeptID, the below statement makes use of another clause HAVING.
HAVING is like a WHERE clause and is used for filtering the data. But HAVING clause works on aggregated data. You can not use a WHERE clause after performing a GROUP BY. To filter on aggregated data, use HAVING.
In the below statement, HAVING Count(*) > 3 means only those DeptIDs will be returned for which the Employee Count is greater than 3.
Select Count(*) as Cnt, DeptID , MAX(EmployeeID) as MaxEmpID FROM Employees
GROUP BY DeptID
HAVING Count(*) > 3
12) JOINS - Joins can be used to combine data from two or more tables.
INNER JOIN in the below statement combines data from Employees and Dept tables. It matches data on the common column in the two tables - DeptID. This column is specified in the ON statement.
Aliases for the tables - Emp for Employees and Dep for Dept are created so that we can clearly indicate which column belongs to which table.
INNER JOIN will fetch matching data from the two tables. For ex - if there is a record in Employee Table for DeptID - 999 and this DeptID value does not exist in Dept Table, this record will not be fetched.
Select EmployeeID, DeptName
FROM Employees Emp INNER JOIN Dept Dep
ON Emp.DeptID = Dep.DeptID
A LEFT Join on the other hand fetches all data from the LEFT table ( the one before the LEFT JOIN keywords - Dept in our example.) Here the query fetches all data from Dept table and only matching data from Employees table. So, if DeptID - 9999 exists in Dept table and not in Employees table, it will still fetch the record but put NULL values in the columns selected from Employee table.
Select Dep.DeptID, Dep.DeptName, Emp.DeptID
FROM Dept Dep LEFT JOIN (Select DISTINCT DeptID FROM Employees) Emp
ON Emp.DeptID = Dep.DeptID
13) Sub-queries - A Select Query within another Select Query is called a Sub-query.
In the below statement, the subquery (within brackets) - first selects the Top2 DeptID from Dept table base don descending order of sorting for DeptName. The main SQL Query then checks in the Employees table for those DeptIDs and outputs only those records which have the same DeptID values as those fetched by the subquery.
Since the data can change, hence the values for the Top 2 DeptIDs can change with time and so we need to use the subquery in this case.
SELECT * FROM Employees
WHERE DeptID IN (Select Top 2 DeptID
FROM Dept
ORDER BY DeptName DESC
14) Conditional Statements - An If-Then-Else condition can be implemented in a SQL Query using the CASE statement as below.
SELECT EmployeeID, Salary,
CASE WHEN Salary > 90000 THEN 'Y'
WHEN Salary BETWEEN 45000 and 85000 THEN 'N'
ELSE 'X' END as SalFlag
FROM Employees
Wow, so now you already know how to write SQL Queries. You are already a SQL Developer ow and can comfortably catch on advanced concepts to hone your skills.
Keep checking our blog and YouTube channel for regular posts and videos on all things SQL.
And Congratulations on getting started on your SQL journey!