SELECT Queries in SQL Server (DQL)

DQL is used to READ data.

SELECT -> get data from a table

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Employees;
GO

CREATE TABLE Employees
(
    EmpID      INT PRIMARY KEY,
    Name       VARCHAR(50),
    Department VARCHAR(30),
    City       VARCHAR(30),
    Salary     INT,
    Gender     CHAR(1)
);

INSERT INTO Employees VALUES
(1,  'Ravi',   'IT',      'Hyderabad', 50000, 'M'),
(2,  'Sita',   'HR',      'Chennai',   40000, 'F'),
(3,  'John',   'IT',      'Bengaluru', 60000, 'M'),
(4,  'Meena',  'Sales',   'Hyderabad', 35000, 'F'),
(5,  'Arjun',  'Sales',   'Mumbai',    45000, 'M'),
(6,  'Pooja',  'IT',      'Hyderabad', 55000, 'F'),
(7,  'Kiran',  'HR',      'Mumbai',    38000, 'M'),
(8,  'Divya',  'Finance', 'Chennai',   52000, 'F'),
(9,  'Rahul',  'Finance', 'Bengaluru', 48000, 'M'),
(10, 'Neha',   'IT',      NULL,        65000, 'F');   -- City is unknown
GO

(10 rows affected)

All columns, all rows

SELECT * FROM Employees;

Only some columns

SELECT Name, Salary FROM Employees;

Column alias (a new name for the output column)

SELECT Name AS EmployeeName, Salary AS MonthlySalary FROM Employees;

Calculation in SELECT

SELECT Name, Salary, Salary * 12 AS YearlySalary FROM Employees;

WHERE - filter rows

SELECT * FROM Employees WHERE Department = 'IT';

Comparison operators: = <> > < >= <=

SELECT * FROM Employees WHERE Salary > 50000;
SELECT * FROM Employees WHERE Department <> 'IT';     -- not equal

And - both conditions must be true

SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 55000;

Or - any one condition is enough

SELECT * FROM Employees WHERE City = 'Mumbai' OR City = 'Chennai';

In - match any value in a list

SELECT * FROM Employees WHERE City IN ('Mumbai', 'Chennai');

NOT IN

SELECT * FROM Employees WHERE Department NOT IN ('IT', 'HR');

BETWEEN - a range (both ends included)

SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 50000;

LIKE - search text

% = any number of characters _ = exactly one character

SELECT * FROM Employees WHERE Name LIKE 'R%';      -- starts with R
SELECT * FROM Employees WHERE Name LIKE '%a';      -- ends with a
SELECT * FROM Employees WHERE Name LIKE '%ee%';    -- contains ee
SELECT * FROM Employees WHERE Name LIKE '____';    -- exactly 4 letters

IS NULL / IS NOT NULL (do NOT use = NULL)

SELECT * FROM Employees WHERE City IS NULL;
SELECT * FROM Employees WHERE City IS NOT NULL;

DISTINCT - remove duplicates

SELECT DISTINCT Department FROM Employees;

TOP - first N rows

SELECT TOP 3 * FROM Employees;

ORDER BY - sort the result

SELECT * FROM Employees ORDER BY Salary;          -- low to high (ASC is default)
SELECT * FROM Employees ORDER BY Salary DESC;     -- high to low

TOP with ORDER BY - 3 highest salaries

SELECT TOP 3 Name, Salary FROM Employees ORDER BY Salary DESC;

Replace NULL with a value

SELECT Name, ISNULL(City, 'Not Known') AS City FROM Employees;