آموزش ORDER BY و COUNT در SQL Server با ۱۵ مثال کاربردی

CREATE TABLE Employees (
  EmployeeId INT PRIMARY KEY,
  FullName   NVARCHAR(50),
  Department NVARCHAR(30),
  City       NVARCHAR(30) NULL,
  Salary     INT
);

INSERT INTO Employees VALUES
 (1, N'Ali',   N'IT',    N'Tehran',  900), (2, N'Sara',  N'IT',    N'Shiraz', 1200),
 (3, N'Reza',  N'HR',    NULL,       700), (4, N'Mina',  N'Sales', N'Tehran', 800),
 (5, N'Omid',  N'Sales', N'Tehran',  800), (6, N'Neda',  N'IT',    NULL,     1100);

SELECT FullName, Salary FROM Employees ORDER BY Salary;        -- ASC

SELECT FullName, Salary FROM Employees ORDER BY Salary DESC;

SELECT Department, FullName, Salary
FROM Employees
ORDER BY Department ASC, Salary DESC;

SELECT FullName, Salary * 12 AS YearlySalary
FROM Employees
ORDER BY YearlySalary DESC;   -- در ORDER BY می‌توان از Alias استفاده کرد

SELECT FullName, City
FROM Employees
ORDER BY CASE WHEN City IS NULL THEN 1 ELSE 0 END, City;   -- NULLها آخر

SELECT TOP (3) FullName, Salary FROM Employees ORDER BY Salary DESC;

SELECT FullName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;   -- صفحه‌ی دوم با اندازه‌ی ۲

SELECT COUNT(*) AS Total FROM Employees;        -- 6

SELECT COUNT(City) AS WithCity FROM Employees;  -- 4 (دو NULL شمرده نمی‌شود)

SELECT COUNT(DISTINCT Department) AS DeptCount FROM Employees;  -- 3

SELECT COUNT(*) AS ITCount FROM Employees WHERE Department = N'IT';  -- 3

SELECT Department, COUNT(*) AS Cnt
FROM Employees
GROUP BY Department;

SELECT TOP (1) Department, COUNT(*) AS Cnt
FROM Employees
GROUP BY Department
ORDER BY COUNT(*) DESC;

SELECT Department, COUNT(*) AS Cnt
FROM Employees
GROUP BY Department
HAVING COUNT(*) >= 2
ORDER BY Cnt DESC, Department;

SELECT
  COUNT(*)                                        AS AllEmployees,
  COUNT(CASE WHEN Salary >= 1000 THEN 1 END)      AS HighPaid,
  SUM(CASE WHEN City IS NULL THEN 1 ELSE 0 END)   AS NoCity
FROM Employees;